--------------------------------------------------------------------------------
-- @name: optimizer with no stats
-- @author: dion cho
-- @note: test case on the optimizer's behavior without statistics
--------------------------------------------------------------------------------

set pages 100
set trimspool on
set timing off

-- case1: table has no stats
drop table t1 purge;

create table t1
as
select level as c1, rpad('x',100) as c2
from dual
connect by level <= 10000
;

-- dynamic sampling is done!
@para optimizer_dynamic_sampling

select /*+ gather_plan_statistics */ count(*) 
from t1 where c1 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- disable dynamic sampling
alter session set optimizer_dynamic_sampling = 0;

select /*+ gather_plan_statistics */ count(*) 
from t1 where c1 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- alter optimizer mode to choose
alter session set optimizer_mode = choose;

select /*+ gather_plan_statistics */ count(*) 
from t1 where c1 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- apply hint --> return to cbo
select /*+ gather_plan_statistics full(t1) */ count(*) 
from t1 where c1 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- where the value of e-rows=144 come from?
--http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#i41866
--http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf

set serveroutput on
exec show_space('T1');
set serveroutput off
/*
Free Blocks.............................
Total Blocks............................1280
Total Bytes.............................10485760
Total MBytes............................10
Unused Blocks...........................1104
Unused Bytes............................9043968
Last Used Ext FileId....................9
Last Used Ext BlockId...................24329
Last Used Block.........................176
*/

col bas_card new_value bas_card
select 
  176 * ( 8192 - 24 ) / 100 as bas_card
from dual;

col density new_value density
select
  1 / ( &bas_card / 32 ) as density
from dual;

select &bas_card * 
      case 
        when &density < 0.01 then 0.01
        else &density
      end as card
from dual;


alter session set tracefile_identifier = 'case1';
@trace_on 10053 1
select /*+ full(t1) */ count(*) 
from t1 where c1 = 1;
@trace_off
@trace_file
ho copy &trace_file case1.trc


-- case2: table has stats but column has no stats
exec dbms_stats.gather_table_stats(user, 't1', -
    method_opt=>'for columns c2 size auto', no_invalidate=>false);

-- alter optimizer mode to all_rows
alter session set optimizer_mode = all_rows;

set serveroutput off


select /*+ gather_plan_statistics */ count(*) 
from t1 where c1 = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

select 10000*0.01 from dual;

alter system flush shared_pool;
alter session set tracefile_identifier = 'case2';
@trace_on 10053 1
select /*+ full(t1) */ count(*) 
from t1 where c1 = 1;
@trace_off
@trace_file
ho copy &trace_file case2.trc


@diff case1.trc case2.trc


set timing on
